﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Linq;
using DBUtil.Entity;
using DBUtil.SPI;

namespace DBUtil
{
    public partial class DB : IDB
    {
        #region IDB 实现

        #region DbProvider
        private DbProviderFactory _dbfactory = null;
        private DbProviderFactory DBFactory
        {
            get { return _dbfactory; }
        }

        private string _providerName = null;
        public string ProviderName
        {
            get { return _providerName; }
        }
        #endregion

        #region 属性

        private DataBaseType _DBaseType = DataBaseType.MsSql;
        public DataBaseType DBaseType
        {
            get { return _DBaseType; }
        }

        private string _connectionString = string.Empty;
        public string ConnectionString
        {
            get
            {
                if (string.IsNullOrWhiteSpace(_connectionString))
                {
                    if (ConfigurationManager.ConnectionStrings[DBaseType.ToString()] != null)
                    {
                        this._connectionString = ConfigurationManager.ConnectionStrings[DBaseType.ToString()].ConnectionString;
                    }
                }
                return _connectionString;
            }
        }

        private string _paramCharacter = "@";
        public string ParamCharacter
        {
            get { return _paramCharacter; }
        }

        private volatile Hashtable _cache = new Hashtable(StringComparer.OrdinalIgnoreCase);

        /// <summary>
        /// Hashtable缓存
        /// </summary>
        private Hashtable Cache
        {
            get
            {
                return _cache;
            }

        }
        #endregion

        #region 表、列名数据 属性

        /// <summary>
        /// 获取对应数据库中所有的表名
        /// </summary>
        public List<string> TableNames
        {
            get
            {
                if (!Cache.ContainsKey("TableNames"))
                {
                    Cache["TableNames"] = new DBInfo(this).TableNames;
                }
                return (Cache["TableNames"] as List<string>) ?? new List<string>();
            }
        }

        /// <summary>
        /// 获取数据库 表名 及其对应的所有 列名
        /// </summary>
        public Dictionary<string, List<string>> Table_Column_Names
        {
            get
            {
                if (!Cache.ContainsKey("Table_Column_Names"))
                {
                    Dictionary<string, List<string>> dict_tc_Names = new Dictionary<string, List<string>>(StringComparer.OrdinalIgnoreCase);
                    List<string> lstTableNames = TableNames;
                    foreach (var tableName in lstTableNames)
                    {
                        List<ColumnInfo> lstColInfo = new DBInfo(this).GetAllColumnInfo(tableName);
                        dict_tc_Names.Add(tableName, lstColInfo.ConvertAll(t => t.ColumnName).ToList());
                    }
                    Cache["Table_Column_Names"] = dict_tc_Names;
                }
                return (Cache["Table_Column_Names"] as Dictionary<string, List<string>>) ?? new Dictionary<string, List<string>>(StringComparer.OrdinalIgnoreCase);
            }
        }

        /// <summary>
        /// 获取数据库 表名 及其对应的所有 列信息
        /// </summary>
        public Dictionary<string, List<ColumnInfo>> Table_Column_Infos
        {
            get
            {
                if (!Cache.ContainsKey("Table_Column_Infos"))
                {
                    Dictionary<string, List<ColumnInfo>> dict_tc_Infos = new Dictionary<string, List<ColumnInfo>>(StringComparer.OrdinalIgnoreCase);

                    List<string> lstTableNames = TableNames;
                    foreach (var tableName in lstTableNames)
                    {
                        List<ColumnInfo> lstColInfo = new DBInfo(this).GetAllColumnInfo(tableName);
                        dict_tc_Infos.Add(tableName, lstColInfo);

                        foreach (ColumnInfo cInfo in lstColInfo)
                        {
                            Cache["Table_Column_Infos" + "_" + tableName + "_" + cInfo.ColumnName] = cInfo;
                        }
                    }

                    Cache["Table_Column_Infos"] = dict_tc_Infos;
                }
                return (Cache["Table_Column_Infos"] as Dictionary<string, List<ColumnInfo>>) ?? new Dictionary<string, List<ColumnInfo>>();
            }
        }

        /// <summary>
        /// 根据表名 获取其对应的所有 列信息
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>对应表的所有列信息</returns>
        public List<ColumnInfo> this[string tableName]
        {
            get
            {
                return Table_Column_Infos[tableName];
            }
        }

        /// <summary>
        /// 根据 表名、列名 获取其 列信息
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <returns></returns>
        public ColumnInfo this[string tableName, string columnName]
        {
            get
            {
                var dict = Table_Column_Infos;
                string cacheKey = "Table_Column_Infos" + "_" + tableName.ToLower() + "_" + columnName.ToLower();
                return Cache[cacheKey] as ColumnInfo;
            }
        }

        #endregion

        #region 构造函数
        public DB(DataBaseType dataBaseType)
        {
            this._DBaseType = dataBaseType;
            SetProviderName(dataBaseType, string.Empty);
        }
        public DB(DataBaseType dataBaseType, string connectionString)
        {
            this._DBaseType = dataBaseType;
            SetProviderName(dataBaseType, connectionString);
            this._connectionString = connectionString;
        }
        private void SetProviderName(DataBaseType dataBaseType, string connectionString)
        {
            switch (dataBaseType)
            {
                case DataBaseType.MsSql:
                    _providerName = "System.Data.SqlClient";
                    _paramCharacter = "@";
                    break;
                case DataBaseType.MySql:
                    _providerName = "MySql.Data.MySqlClient";
                    _paramCharacter = "@";
                    break;
                case DataBaseType.Oracle:
                    _providerName = "System.Data.OracleClient";
                    _paramCharacter = ":";
                    break;
                case DataBaseType.OracleManaged:
                    _providerName = "Oracle.ManagedDataAccess.Client";
                    _paramCharacter = ":";
                    break;
                case DataBaseType.Sqlite:
                    _providerName = "System.Data.SQLite";
                    _paramCharacter = "@";
                    break;
                default:
                    _providerName = "System.Data.SqlClient";
                    _paramCharacter = "@";
                    break;
            }
            if (!string.IsNullOrWhiteSpace(connectionString))
            {
                this._connectionString = connectionString;
            }
            else if (ConfigurationManager.ConnectionStrings[dataBaseType.ToString()] != null)
            {
                this._connectionString = ConfigurationManager.ConnectionStrings[dataBaseType.ToString()].ConnectionString;
            }
            else
            {
                throw new ArgumentNullException("connectionString", dataBaseType.ToString() + "的connectionString不能为空！");
            }

            try
            {
                _dbfactory = DbProviderFactories.GetFactory(ProviderName);
            }
            catch (ConfigurationErrorsException ex)
            {

                throw new ConfigurationErrorsException(ex.Message + "(" + ProviderName + ")", ex);
            }

        }


        #endregion

        #region Ado.Net 对象

        private DbConnection _DBConn = null;
        internal DbConnection DBConn
        {
            get
            {
                if (_DBConn == null || string.IsNullOrEmpty(_DBConn.ConnectionString))
                {
                    _DBConn = DBFactory.CreateConnection();
                    _DBConn.ConnectionString = ConnectionString;
                }
                return _DBConn;
            }
        }

        private DbCommand _DBCmd = null;
        private DbCommand DBCmd
        {
            get
            {
                if (_DBCmd == null)
                {
                    _DBCmd = DBFactory.CreateCommand();
                    _DBCmd.Connection = DBConn;
                }
                return _DBCmd;
            }
        }


        private DbDataAdapter GetDataAdapter(DbCommand dbCmd)
        {
            DbDataAdapter dbadapter = DBFactory.CreateDataAdapter();
            dbadapter.SelectCommand = dbCmd;
            return dbadapter;
        }

        private DbParameter CreateParameter()
        {
            DbParameter dbparameter = DBFactory.CreateParameter();
            return dbparameter;
        }

        #endregion

        #region Bool值返回
        public bool ValidateSql(string strSql, out string msg)
        {
            bool bResult = false;
            msg = string.Empty;
            using (DbConnection conn = DBConn)
            {
                DbCommand cmd = DBCmd;
                cmd.Connection = conn;
                conn.Open();
                try
                {
                    if (DBaseType == DataBaseType.MsSql)
                    {
                        cmd.CommandText = "set noexec on;";
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = strSql;
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "set noexec off;";
                        cmd.ExecuteNonQuery();
                    }
                    else if (DBaseType == DataBaseType.Oracle || DBaseType == DataBaseType.OracleManaged)
                    {
                        cmd.CommandText = "explain plan for " + strSql;
                    }
                    else
                    {
                        throw new NotImplementedException("未能实现" + DBaseType + "方式的验证方法！");
                    }
                    cmd.ExecuteNonQuery();
                    bResult = true;
                }
                catch (Exception ex)
                {
                    msg = ex.Message;
                    bResult = false;
                }
                finally
                {
                    cmd.Dispose();
                    conn.Close();
                }
            }
            return bResult;
        }

        public bool Exists(string strSql, params System.Data.IDataParameter[] cmdParms)
        {
            object obj = GetSingle<object>(strSql, 30, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #endregion

        #region 单值、DataRow、DataTable、List<DataTable>、DbDataReader 返回
        public TReturn GetSingle<TReturn>(string strSql, int times, params System.Data.IDataParameter[] cmdParms)
        {
            using (DbConnection connection = DBConn)
            {
                using (DbCommand cmd = DBCmd)
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, strSql, cmdParms, times);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        return  obj.ConvertTo<TReturn>();
                    }
                    catch (Exception ex)
                    {
                        connection.Close();

                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        connection.Close();
                    }
                }
            }
        }

        public DataRow QueryRow(string strSql, int times = 30, params System.Data.IDataParameter[] cmdParms)
        {
            DataTable data = QueryDS(strSql, times, cmdParms).FirstOrDefault();
            if (data != null)
            {
                return data.Rows[0];
            }
            return null;
        }

        public DataTable QueryTable(string strSql, int times = 30, params System.Data.IDataParameter[] cmdParms)
        {
            return QueryDS(strSql, times, cmdParms).FirstOrDefault();
        }

        public List<DataTable> QueryDS(string strSql, int times = 30, params System.Data.IDataParameter[] cmdParms)
        {
            using (DbConnection connection = DBConn)
            {
                DbCommand cmd = connection.CreateCommand();
                PrepareCommand(cmd, connection, null, strSql, cmdParms, times);
                using (DbDataAdapter da = GetDataAdapter(cmd))
                {
                    List<DataTable> lstTab = new List<DataTable>();
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();

                        if (ds != null && ds.Tables.Count > 0)
                        {
                            foreach (DataTable table in ds.Tables)
                            {
                                lstTab.Add(table);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        connection.Close();

                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        connection.Close();
                    }
                    return lstTab;
                }
            }
        }

        public DbDataReader ExecuteReader(string strSql, int times = 30, params System.Data.IDataParameter[] cmdParms)
        {
            DbConnection connection = DBConn;
            DbCommand cmd = DBCmd;
            try
            {
                PrepareCommand(cmd, connection, null, strSql, cmdParms, times);
                DbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 插入、更新、删除 执行命令/执行存储过程
        public int ExecuteSql(string strSql, int times = 30, params System.Data.IDataParameter[] cmdParms)
        {
            using (DbConnection connection = DBConn)
            {
                using (DbCommand cmd = DBCmd)
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, strSql, cmdParms, times);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        cmd.Dispose();
                        connection.Close();
                    }
                }
            }
        }

        public int ExecuteSqlTran(List<string> SqlCmdList)
        {
            using (DbConnection conn = DBConn)
            {
                conn.Open();
                DbCommand cmd = DBCmd;
                cmd.Connection = conn;
                DbTransaction tran = conn.BeginTransaction();
                cmd.Transaction = tran;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SqlCmdList.Count; n++)
                    {
                        string strsql = SqlCmdList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tran.Commit();
                    return count;
                }
                catch
                {
                    tran.Rollback();
                    return -1;
                }
            }
        }

        public int ExecuteSqlTran(Hashtable SqlCmdList)
        {
            using (DbConnection conn = DBConn)
            {
                conn.Open();
                using (DbTransaction trans = conn.BeginTransaction())
                {
                    DbCommand cmd = DBCmd;
                    try
                    {
                        int count = 0;
                        //循环
                        foreach (DictionaryEntry myDE in SqlCmdList)
                        {
                            string cmdText = myDE.Key.ToString();
                            DbParameter[] cmdParms = ((List<DbParameter>)myDE.Value).ToArray();
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            count += cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                        return count;
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        return -1;
                    }
                }
            }
        }

        public DataSet RunProcedure(string storedProcName, int times = 30, params IDataParameter[] parameters)
        {
            DataSet ds = new DataSet("ds");
            using (DbConnection conn = DBConn)
            {
                conn.Open();
                DbCommand cmd = DBCmd;
                PrepareCommand(cmd, conn, null, storedProcName, parameters, times, CommandType.StoredProcedure);
                DataAdapter adapter = GetDataAdapter(cmd);
                adapter.Fill(ds);
            }
            return ds;
        }

        #endregion

        #region private static
        private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, string cmdText, IDataParameter[] cmdParms, int times = 30, CommandType cmdType = CommandType.Text)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = cmdType;
            if (times > 0)//（以秒为单位）。默认为 30 秒
            {
                cmd.CommandTimeout = times;
            }
            else
            {
                cmd.CommandTimeout = 30;
            }
            if (cmdParms != null && cmdParms.Length > 0)
            {
                foreach (IDataParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput
                        || parameter.Direction == ParameterDirection.Input)
                        && (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        #endregion

        #region 调用 IDB 实现方法
        public bool Exists(string strSql)
        {
            return Exists(strSql, null);
        }

        public TReturn GetSingle<TReturn>(string strSql, int times = 30)
        {
            return GetSingle<TReturn>(strSql, times, null);
        }

        public int ExecuteSql(string strSql, params IDataParameter[] cmdParms)
        {
            return ExecuteSql(strSql, 30, cmdParms);
        }
        #endregion

        #endregion
    }
}
